insert overwrite table  jms_dm.dm_end_dispatch_third_loss_detail_dt 
select 
     tt.waybill_no
    ,tt.collect_time as taking_time
    ,t4.scantime as collect_in_time--入库时间
    ,tt.create_time 
    ,tt.date_time  as closing_time --结案时间
    ,tt.exception_desc
    ,tt.first_duty_network_code  as res_network_code1 
    ,net1.name                  as res_network_name1 
    ,net1.financial_center_code as res_agent_code1 
    ,net1.financial_center_desc as res_agent_name1 
    ,net1.fran_code             as res_fran_code1 
    ,net1.fran_name             as res_fran_name1 
    ,net1.virt_code             as res_virt_code1 
    ,net1.virt_name             as res_virt_name1 
    ,tt.second_duty_network_code as res_network_code2 
    ,net2.name                   as res_network_name2 
    ,net2.financial_center_code  as res_agent_code2 
    ,net2.financial_center_desc  as res_agent_name2 
    ,net2.fran_code              as res_fran_code2 
    ,net2.fran_name              as res_fran_name2 
    ,net2.virt_code              as res_virt_code2 
    ,net2.virt_name              as res_virt_name2 
    ,tt.third_duty_network_code  as res_network_code3 
    ,net3.name                   as res_network_name3 
    ,net3.financial_center_code  as res_agent_code3 
    ,net3.financial_center_desc  as res_agent_name3 
    ,net3.fran_code              as res_fran_code3 
    ,net3.fran_name              as res_fran_name3 
    ,net3.virt_code              as res_virt_code3 
    ,net3.virt_name              as res_virt_name3 
    ,tt.four_duty_network_code      as res_network_code4 
    ,net4.name                   as res_network_name4 
    ,net4.financial_center_code  as res_agent_code4 
    ,net4.financial_center_desc  as res_agent_name4 
    ,net4.fran_code              as res_fran_code4 
    ,net4.fran_name              as res_fran_name4 
    ,net4.virt_code              as res_virt_code4 
    ,net4.virt_name              as res_virt_name4 
    ,tt.five_duty_network_code      as res_network_code5 
    ,net5.name                   as res_network_name5 
    ,net5.financial_center_code  as res_agent_code5 
    ,net5.financial_center_desc  as res_agent_name5 
    ,net5.fran_code              as res_fran_code5 
    ,net5.fran_name              as res_fran_name5 
    ,net5.virt_code              as res_virt_code5 
    ,net5.virt_name              as res_virt_name5 
    ,tt.award_amount_total  as award_amount_total 
    ,t4.store_code
    ,t4.store_name
    ,t4.business_id 
    ,t4.business_name
    ,date(tt.date_time) as  date_time
    ,tt.first_duty_pay_amount  as res_network_amount1
    ,tt.second_duty_pay_amount as res_network_amount2
    ,tt.third_duty_pay_amount  as res_network_amount3
    ,tt.four_duty_pay_amount   as res_network_amount4
    ,tt.five_duty_pay_amount   as res_network_amount5
    ,tt.network_count
    ,date(tt.date_time) as dt
from  
( 
    select 
    waybill_no
    ,collect_time
    ,create_time
    ,date_time 
    ,exception_desc
    ,first_duty_network_code
    ,second_duty_network_code
    ,third_duty_network_code
    ,four_duty_network_code
    ,five_duty_network_code
    ,award_amount_total        --赔付金额
    ,first_duty_pay_amount  
    ,second_duty_pay_amount 
    ,third_duty_pay_amount  
    ,four_duty_pay_amount   
    ,five_duty_pay_amount 
    ,network_count    
    from jms_dm.dm_sqs_loss_detail_dt
    where
    loss_type ='遗失'
    and is_filter =1
    and first_type_code = '03' and second_type_code = '0317'
    and dt between trunc('{{ execution_date  | cst_ds }}','MM') and  '{{ execution_date  | cst_ds }}'
)tt left join 
(
    select 
    billcode ,scansitecode ,store_code ,store_name ,business_id ,business_name ,scantime
    from 
    (
    select billcode ,scansitecode ,store_code ,store_name ,business_id ,business_name ,scantime,
           row_number() over(partition by billcode  order by dt desc) as rk 
    from jms_dm.dm_network_end_dispatch_diff_detail
    where dt between date_add(trunc('{{ execution_date  | cst_ds }}','MM'),-40) and  '{{ execution_date  | cst_ds }}'
    )t where rk=1
)t4 on t4.billcode=tt.waybill_no -- and t4.scansitecode=tt.responsibility_network_code
left join jms_dim.dim_network_whole_massage net1 on net1.code =tt.first_duty_network_code  
left join jms_dim.dim_network_whole_massage net2 on net2.code =tt.second_duty_network_code  
left join jms_dim.dim_network_whole_massage net3 on net3.code =tt.third_duty_network_code  
left join jms_dim.dim_network_whole_massage net4 on net4.code =tt.four_duty_network_code  
left join jms_dim.dim_network_whole_massage net5 on net5.code =tt.five_duty_network_code  
distribute by pmod(hash(rand()), 3);
----------------------------------------------------------------------------------------------------------------------------
with detail as (
    select
        waybill_no
         ,split(netd.network_info,':')[0] as network_code
         ,split(netd.network_info,':')[1] as network_amount
         ,store_code
         ,store_name
         ,business_id
         ,business_name
         ,date_time
         ,network_count
    from
        (
            select
                waybill_no
                 ,dt as date_time
                 ,store_code
                 ,store_name
                 ,business_id
                 ,business_name
                 ,ARRAY(
                    concat(res_network_code1,':',res_network_amount1),
                    concat(res_network_code2,':',res_network_amount2),
                    concat(res_network_code3,':',res_network_amount3),
                    concat(res_network_code4,':',res_network_amount4),
                    concat(res_network_code5,':',res_network_amount5)
                ) as duty_arr
                 ,network_count --网点数
            from  jms_dm.dm_end_dispatch_third_loss_detail_dt
            where dt between trunc('{{ execution_date  | cst_ds }}','MM') and  '{{ execution_date  | cst_ds }}'
        )t LATERAL VIEW explode(duty_arr) netd as network_info
where netd.network_info is not null
    )



insert overwrite table jms_dm.dm_end_dispatch_third_loss_cnt_dt
select
    t1.date_time
     ,net.financial_center_code as res_agent_code
     ,net.financial_center_desc as res_agent_name
     ,net.fran_code as res_fran_code
     ,net.fran_name as res_fran_name
     ,nvl(net.virt_code,net.financial_center_code) as res_virt_code
     ,nvl(net.virt_name,net.financial_center_desc) as res_virt_name
     ,net.provider_id as res_provider_id
     ,net.provider_desc as res_provider_desc
     ,net.city_id as res_city_id
     ,net.city_desc as res_city_desc
     ,net.zone_code as res_zone_code
     ,net.zone_name as res_zone_name
     ,t1.res_network_code
     ,net.name as res_network_name
     ,t1.store_code
     ,t1.store_name
     ,t1.business_id
     ,t1.business_name
     ,t1.loss_cnt
     ,t2.store_in_cnt
     ,t1.date_time
     ,max(t2.agent_in_cnt) over(partition by net.financial_center_code,t1.date_time) agent_in_cnt
     ,t2.source as source
     ,t1.date_time as dt
from
    (
        select
            date_time
             ,network_code as res_network_code
             ,business_id
             ,max(business_name) as business_name
             ,store_code
             ,max(store_name) as store_name
             ,sum(cast ((1/network_count) as decimal(22,2))) as loss_cnt
        from detail
        where date_time between trunc('{{ execution_date  | cst_ds }}','MM') and  '{{ execution_date  | cst_ds }}'
        group by date_time,network_code,business_id,store_code
    )t1
        left join
    (
        select
            scansitecode ,store_code  ,business_id ,store_in_cnt,dt,agent_code,source,
            sum(store_in_cnt) over(partition by agent_code,dt) as agent_in_cnt
        from
            (
                select scansitecode ,store_code  ,business_id ,count(1) as store_in_cnt,dt,agent_code,max(source) as source
                from jms_dm.dm_network_end_dispatch_diff_detail
                where dt between trunc('{{ execution_date  | cst_ds }}','MM') and  '{{ execution_date  | cst_ds }}'
                group by dt,scansitecode ,store_code ,business_id,agent_code
            )t
    )t2 on t1.res_network_code=t2.scansitecode and t2.store_code=t1.store_code and  t1.business_id=t2.business_id  and t1.date_time=t2.dt
        left join jms_dim.dim_network_whole_massage net on net.code =t1.res_network_code
distribute by pmod(hash(rand()), 3);